package com.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.app.util.sys.UUIDUtil;

/**
 * 
 * TODO
 * @author zoe
 * 2016年4月21日
 */
public abstract class Import97ExcelService extends Import97ExcelBaseService {
	
	@Override
	public String setSourseExcelPath(){
		return "C:\\Users\\Administrator\\Desktop\\工作簿1.xls";
		
	}
	
	public HSSFWorkbook getSourceWb() throws IOException{
		FileInputStream fileInputStream = new FileInputStream(setSourseExcelPath());
		return new HSSFWorkbook(fileInputStream);
	}
	
	public void doExcelUrlToKey(){
		File excelFile = new File(setSourseExcelPath());
		
		FileInputStream fileInputStream;
		try {
			fileInputStream = new FileInputStream(excelFile);
			
			HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileInputStream);
			
			doUrlToPicKey(hssfWorkbook);
			
			File outFile = new File("C:\\Users\\Administrator\\Desktop\\工作簿2.xls");
			FileOutputStream fos = new FileOutputStream(outFile);
			hssfWorkbook.write(fos);
			fos.close();
			
			fileInputStream.close();
			
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	private void doUrlToPicKey(HSSFWorkbook hssfWorkbook) throws IOException {
		int numberOfSheets = hssfWorkbook.getNumberOfSheets();
		for (int i = 0; i < numberOfSheets; i++) {
			HSSFSheet sheetAt = hssfWorkbook.getSheetAt(i);
			int physicalNumberOfRows = sheetAt.getPhysicalNumberOfRows();
			for (int j = 1; j < physicalNumberOfRows; j++) {
				HSSFRow row = sheetAt.getRow(j);
				HSSFCell cell = row.getCell(4);
				String picUrl = cell.getStringCellValue();
				cell.setCellValue(getPicUploadKey());
			}
		}
	}
	
	private String getPicUploadKey(){
		return UUIDUtil.getUUID();
	};
}
